import orm
def show_products():
    sql = f"""
            select id,name,price,discount from products
        """
    data = orm.read_db(sql)
    if data != ():
        print(f'----编码----名称----价格----折扣----')
        for product in data:
            print(f'\t{product[0]}\t   {product[1]}\t  {product[2]}\t  {product[3]}')
def select_products():
    name = input('请输入要查询的商品名称：')
    sql = f"select price,discount from products where name='{name}'"
    data = orm.read_db(sql)
    if data != ():
        print(f'----价格----折扣----')
        print(f'\t{data[0][0]}\t   {data[0][1]}')
    else:
        print('商品不存在！')
def add_products():
    name = input('请输入新增商品名称：')
    price = input("请输入商品价格：")
    discount = input("请输入商品折扣（折扣输入在0-1之间）：")
    read_sql = f"select * from products where name='{name}'"
    insert_sql = f"insert into products values(null,'{name}',{price},{discount})"
    read_outcome = orm.read_db(read_sql)
    if read_outcome == ():
        orm.wirte_db(insert_sql)
        print('商品新增成功！！！')
    else:
        print('商品已存在，请勿重复添加！！！')
def modify_products():
    id=input('请输入要修改价格和折扣的商品编号：')
    price=input('请输入修改后的价格：')
    discount=input('请输入修改后的折扣（折扣输入在0-1之间）：')
    read_sql=f"select id from products where id={id}"
    update_sql=f"update products set price={price} and discount={discount} WHERE id={id}"
    data=orm.read_db(read_sql)
    if data!=():
        orm.wirte_db(update_sql)
        print("修改成功！！！")
    else:
        print("商品编号不存在！！！")
def del_products():
    id = input('请输入要删除的商品编号：')
    read_sql = f"select id from products where id={id}"
    del_sql = f"delete from products where id={id}"
    data = orm.read_db(read_sql)
    if data != ():
        orm.wirte_db(del_sql)
        print("删除成功！！！")
    else:
        print("商品编号不存在！！！")
def show_orders():
    sql = f"""
                select num,id,count,amount from orders
            """
    data = orm.read_db(sql)
    if data != ():
        print(f'----订单编号----商品编号----数量----金额----')
        for order in data:
            print(f'\t{order[0]}\t    {order[1]}\t   {order[2]}\t   {order[3]}')
def del_orders():
    num = input('请输入要删除的订单编号：')
    read_sql = f"select num from orders where num={num}"
    del_sql = f"delete from orders where num={num}"
    data = orm.read_db(read_sql)
    if data != ():
        orm.wirte_db(del_sql)
        print("删除成功！！！")
    else:
        print("订单编号不存在！！！")
def orders_count():
    sql1= f"select sum(count) from orders"
    sql2=f"select sum(amount) from orders"
    sum_count = orm.read_db(sql1)
    sum_amount=orm.read_db(sql2)
    print(f"总销量：{sum_count[0][0]}\t总销售额：{sum_amount[0][0]}")
def settlement():
    name=input("请输入购买商品的名称：")
    count=float(input("请输入购买商品数量："))
    sql=f"select id,price,discount from products where name='{name}'"
    data=orm.read_db(sql)
    if data !=():
        id = data[0][0]
        price = data[0][1]
        discount = data[0][2]
        amount = price * discount * count
        sql = f"insert into orders values(null,{id},{count},{amount})"
        rows = orm.wirte_db(sql)
        if rows > 0:
            sql = "SELECT num,id,count,amount from orders order by num desc limit 1"
            data = orm.read_db(sql)
            if data != ():
                print("--------------新增订单显示---------------")
                print(f'----订单编号----商品编号----数量----金额----')
                for order in data:
                    print(f'\t{order[0]}\t    {order[1]}\t   {order[2]}\t   {order[3]}')
        else:
            print("订单增加失败！！！")
    else:
        print("购买商品不存在！！！")
if __name__ == '__main__':
    # show_products()
    # select_products()
    # add_products()
    # modify_products()
    # del_products()
    # show_orders()
    # del_orders()
    # orders_count()
    settlement()

